USE [GestorSQL]

SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

/***Disambling Constraints**/

SET NOCOUNT ON
SET ROWCOUNT 0 

DECLARE @Count int
DECLARE @String nvarchar (1000)
DECLARE @ConstraintName varchar(128)
DECLARE @TableName varchar(128)

SELECT 
		name					 AS constraintname,
		object_name(parent_obj)  AS tablename 
INTO #Const_Table
FROM sysobjects s 
where xtype in ('F')

SELECT @Count = Count(*) FROM #Const_Table

SET ROWCOUNT 1

WHILE @Count > 0
BEGIN
	SELECT @TableName = TableName, @ConstraintName = ConstraintName
	FROM #Const_Table

	SET @String = 'ALTER TABLE ['+ @tablename + '] NOCHECK CONSTRAINT [' + @constraintname +']'

	--Execute the SQL
	exec sp_executesql @string

	--Remove this row from the temp table, since it has now been processed.
	DELETE FROM #Const_Table WHERE ConstraintName = @ConstraintName and TableName = @TableName

	SET @Count = @Count - 1
END -- Loop

drop table #Const_Table

set rowcount 0



/***Deleting Data**/

delete from Caso
delete from Conexion
delete from ConjuntoSolucion
delete from Core
delete from Historia
delete from [Log]
delete from Parametros
delete from Query
delete from [Role]
delete from Solucion
delete from UserInRole
delete from Bitacora
delete from Asignaciones


truncate table [Log]
truncate table ConjuntoSolucion
truncate table UserInRole
truncate table Bitacora
truncate table Asignaciones


DBCC CHECKIDENT('Historia', RESEED, 0)
DBCC CHECKIDENT('Log', RESEED, 0)
DBCC CHECKIDENT('Query', RESEED, 0)
DBCC CHECKIDENT('Solucion', RESEED, 0)
DBCC CHECKIDENT('Bitacora', RESEED, 0)
DBCC CHECKIDENT('Conexion', RESEED, 0)

go
/***Enabling Constraints**/

SET NOCOUNT ON
SET ROWCOUNT 0 

DECLARE @Count int
DECLARE @String nvarchar (1000)
DECLARE @ConstraintName varchar(128)
DECLARE @TableName varchar(128)

SELECT 
		name					 AS constraintname,
		object_name(parent_obj)  AS tablename 
INTO #Const_Table
FROM sysobjects s 
where xtype in ('F')

SELECT @Count = Count(*) FROM #Const_Table

SET ROWCOUNT 1

WHILE @Count > 0
BEGIN
	SELECT @TableName = TableName, @ConstraintName = ConstraintName
	FROM #Const_Table
	SET @String = 'ALTER TABLE ['+ @tablename + '] CHECK CONSTRAINT [' + @constraintname +']'
	exec sp_executesql @string
	DELETE FROM #Const_Table WHERE ConstraintName = @ConstraintName and TableName = @TableName
	SET @Count = @Count - 1
END -- Loop

drop table #Const_Table

set rowcount 0



SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

/*** Crear datos por defecto ***/
INSERT INTO [GestorSQL].[dbo].[Role]([IdRole],[Nombre])VALUES(1,'Analista')
INSERT INTO [GestorSQL].[dbo].[Role]([IdRole],[Nombre])VALUES(2,'Autorizador')
INSERT INTO [GestorSQL].[dbo].[Role]([IdRole],[Nombre])VALUES(3,'Administrador')
INSERT INTO [GestorSQL].[dbo].[Role]([IdRole],[Nombre])VALUES(4,'Consultas')


INSERT INTO [GestorSQL].[dbo].[UserInRole]([Fk_Role],[Usuario])VALUES(1,'aramosb')
INSERT INTO [GestorSQL].[dbo].[UserInRole]([Fk_Role],[Usuario])VALUES(2,'carlosm')
INSERT INTO [GestorSQL].[dbo].[UserInRole]([Fk_Role],[Usuario])VALUES(3,'daosorio')
INSERT INTO [GestorSQL].[dbo].[UserInRole]([Fk_Role],[Usuario])VALUES(4,'jcmendietas')


INSERT INTO [GestorSQL].[dbo].[Core]([Id],[Nombre],[Assembly],[Class],[ConnectionString])VALUES(1,'Sybase','GestorSQL.DataBase.Sybase.dll','GestorSQL.DataBase.Sybase.DataManager','Data Source={0};Provider=SQLOLEDB;Initial Catalog={1};User ID={2};Password={3}')
INSERT INTO [GestorSQL].[dbo].[Core]([Id],[Nombre],[Assembly],[Class],[ConnectionString])VALUES(2,'SqlServer','GestorSQL.DataBase.SqlServer.dll','GestorSQL.DataBase.SqlServer.DataManager','Data Source={0};Provider=SQLOLEDB;Initial Catalog={1};User ID={2};Password={3}')


INSERT INTO [GestorSQL].[dbo].[Conexion]([Fk_Core],[Nombre],[Ambiente],[Servidor],[Usuario],[Contrasena],[BaseDeDatos],[Propiedades])VALUES(2,'sq3_desa_consultas',0,'MYIBMSERVER\DESARROLLO','EUdW26syQ2DMLdzK8DWMDg==','+Q8mf5nXWzVWFu6lRwRZY0ZfKAcf6yIT','GestorSQL',NULL)         
INSERT INTO [GestorSQL].[dbo].[Conexion]([Fk_Core],[Nombre],[Ambiente],[Servidor],[Usuario],[Contrasena],[BaseDeDatos],[Propiedades])VALUES(2,'sq3_prod_consultas',2,'MYIBMSERVER\DESARROLLO','EUdW26syQ2DMLdzK8DWMDg==','+Q8mf5nXWzVWFu6lRwRZY0ZfKAcf6yIT','GestorSQL',NULL)         
INSERT INTO [GestorSQL].[dbo].[Conexion]([Fk_Core],[Nombre],[Ambiente],[Servidor],[Usuario],[Contrasena],[BaseDeDatos],[Propiedades])VALUES(1,'sq1_desa_consultas',0,'127.0.0.1\DESARROLLO','EUdW26syQ2DMLdzK8DWMDg==','+Q8mf5nXWzVWFu6lRwRZY0ZfKAcf6yIT','GestorSQL',NULL)         
INSERT INTO [GestorSQL].[dbo].[Conexion]([Fk_Core],[Nombre],[Ambiente],[Servidor],[Usuario],[Contrasena],[BaseDeDatos],[Propiedades])VALUES(1,'sq1_prod_consultas',2,'127.0.0.1\DESARROLLO','EUdW26syQ2DMLdzK8DWMDg==','+Q8mf5nXWzVWFu6lRwRZY0ZfKAcf6yIT','GestorSQL',NULL)         


INSERT INTO [GestorSQL].[dbo].[Parametros]([Codigo],[Valor],[Descripcion]) VALUES ('DirectorioQueries','C:\LOGGestorSQL','Ruta en la cual se guardan los archivos de los queries')
INSERT INTO [GestorSQL].[dbo].[Parametros]([Codigo],[Valor],[Descripcion]) VALUES ('ServiceTimerInterval','1M','ntervalo de tiempo en el que el servicio de ejecucion revisa procesos pendiente, ###(M = Minutos, H = Horas, S = Segundos)')

INSERT INTO [GestorSQL].[dbo].[Asignaciones]([UsuarioAutorizador],[CasosActivos])VALUES ('carlosm',0)